﻿Imports System.Data.Linq
Imports System.Linq

Public Class CPortController
    Dim objDataContext As New TanNamChinhDataContext
    Public Function SelectRecords(ByVal sPort As String, ByVal idCountry As Integer) As IQueryable 'Table(Of tblPort)
        Try
            'Đọc dữ liệu
            Dim Rowss = SelectAllRecord()

            'Xử lý lọc
            If (sPort <> "") Then
                Rowss = Rowss.Where(Function(p) p.Port.StartsWith(sPort))
            End If
            If (idCountry > 0) Then
                Rowss = Rowss.Where(Function(p) p.ID_Country = idCountry)
            End If

            'Lấy kết quả và trả về
            Dim q As IQueryable = (From p In Rowss
                                  Select New With {p.ID_Port, p.Port, p.ID_Country}).OrderBy(Function(p) p.Port)


            Return (q)

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function SelectAllRecord() As IQueryable(Of tblPort)
        Try
            Dim q As IQueryable = From p In objDataContext.tblPorts
                                  Where p.IsActive = True
                                  Select p
            'Return objDataContext.tblPorts
            Return q
        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function SelectAllRecord3(ByVal sPort As String, ByVal idCountry As Integer) As IQueryable
        Try
            'Đọc dữ liệu
            Dim Rowss = SelectAllRecord()

            'Xử lý lọc
            If (sPort <> "") Then
                Rowss = Rowss.Where(Function(p) p.Port.StartsWith(sPort))
            End If
            If (idCountry > 0) Then
                Rowss = Rowss.Where(Function(p) p.ID_Country = idCountry)
            End If
            Dim result As IQueryable = (From p In Rowss
                    Group Join c In objDataContext.tblCountries On p.ID_Country Equals c.ID_Country
                    Into PortList = Group
                    From countryPort In PortList.DefaultIfEmpty()
                    Order By p.Port Ascending
                    Select p.ID_Port, p.Port, p.ID_Country,
            CountryName = If(countryPort Is Nothing, String.Empty, countryPort.CountryName)
                )
            Return result
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#Region "Code HT"
    ''' <summary>
    ''' Lấy danh sách Port theo Name (Name là duy nhất)
    ''' Create date: 23-12-2013
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectRecordByName(ByVal name As String) As tblPort
        Dim result = (From p In SelectAllRecord()
                 Where p.Port = name
                 Select p).Single
        Return result
    End Function
#End Region

    Public Function SelectRecordByID(ByVal _id As Integer) As tblPort
        Try
            Return (From r In objDataContext.tblPorts
                    Where r.ID_Port = _id
                    Select r).Single
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function InsertRecord(ByVal LocalTable As tblPort) As Integer
        Try
            objDataContext.tblPorts.InsertOnSubmit(LocalTable)
            objDataContext.SubmitChanges()
            Return LocalTable.ID_Country
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Sub UpdateRecord(ByVal LocalTable As tblPort)
        Try
            objDataContext.tblPorts.Attach(LocalTable)
            objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
            objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub UpdateRecordWithoutAttach(ByVal LocalTable As tblPort)
        Try
            objDataContext.Refresh(RefreshMode.KeepCurrentValues, LocalTable)
            objDataContext.SubmitChanges(ConflictMode.ContinueOnConflict)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub DeleteRecord(ByVal _id As Integer)
        Try
            objDataContext.tblPorts.DeleteOnSubmit( _
              (From r In objDataContext.tblPorts _
               Where r.ID_Port = _id).Single)
            objDataContext.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Function CheckExists(ByVal sName As String, ByVal id As Integer) As Boolean
        'cach 1
        Return objDataContext.tblPorts.Any(Function(p) p.Port.ToLower = sName.ToLower And p.ID_Port <> id And p.IsActive = True)
    End Function
    Public Sub New()
        objDataContext = New TanNamChinhDataContext
    End Sub
    Public Sub New(ByVal _obj As TanNamChinhDataContext)
        objDataContext = _obj
    End Sub

#Region "HT Code"
    ''' <summary>
    ''' Chỉ lấy Port Việt Nam
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectVietNamPorts() As IQueryable(Of tblPort)
        Try
            Dim q As IQueryable = From p In objDataContext.tblPorts
                                  Where p.ID_Country = 1
                                  Select p
            Return q
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region

End Class
